package cn.edu.zjusc.ExcelGenerator;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;

import cn.edu.zjusc.ExcelModel.SeatLabelInfo;
import cn.edu.zjusc.ExcelModel.StudentGroupInfo;

@SuppressWarnings("unused")
public class SeatLabelGenerator {
	/**
	 * The spreadsheet to read in
	 */
	private File inputWorkbook;
	/**
	 * The spreadsheet to output
	 */
	private File outputWorkbook;
	
	InputStream inp;
	FileOutputStream fileOut;	
	Workbook wb;
	public void modify(String input, String output,List<SeatLabelInfo> studentList) throws InvalidFormatException, IOException {
		inp = new FileInputStream(input);
		 
		 wb = WorkbookFactory.create(inp);
		 
		 completeData(input,output,studentList);
		 fileOut = new FileOutputStream(output);
		    wb.write(fileOut);
		    fileOut.close();
	}
	
	private void completeData(String input, String output, List<SeatLabelInfo> studentList) throws IOException {
		//居中样式,下划线
		CellStyle style1 = wb.createCellStyle();
		//黑体样式
		CellStyle style2 = wb.createCellStyle();
		CellStyle style3 = wb.createCellStyle();
		//底边线,居中
	    style1.setBorderBottom(CellStyle.BORDER_THIN); 
	    style1.setAlignment(CellStyle.ALIGN_CENTER);
	  //黑体
	    Font font = wb.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style2.setFont(font);
        //下划线
        style3.setBorderBottom(CellStyle.BORDER_THIN); 
        
        System.out.println("----------------------开始生成表格");
        int listSize=studentList.size();
        CreationHelper createHelper = wb.getCreationHelper();
		Sheet sheet = wb.getSheetAt(0);
		//循环创建作为标签，添加数据
		for(int i=0;i<listSize;i++){
			// Create a row and put some cells in it. Rows are 0 based.
			//准考证号
			Row row1 = sheet.createRow((short)(2+11*i));
			row1.createCell(1).setCellValue("准考证号");
			row1.getCell(1).setCellStyle(style2);
			row1.createCell(2).setCellValue(studentList.get(i).getAdmissionTicketID());
			row1.createCell(3);
			row1.createCell(4);
			row1.createCell(5);
		
			sheet.addMergedRegion(new CellRangeAddress(
		            2+11*i, //first row (0-based)
		            2+11*i, //last row  (0-based)
		            2, //first column (0-based)
		            5  //last column  (0-based)
		    ));
			row1.getCell(2).setCellStyle(style1);
			row1.getCell(3).setCellStyle(style3);
			row1.getCell(4).setCellStyle(style3);
			row1.getCell(5).setCellStyle(style3);
			//姓名
			Row row2=sheet.createRow((short)(3+11*i));
			row2.createCell(1).setCellValue("姓名");
			row2.getCell(1).setCellStyle(style2);
			row2.createCell(2).setCellValue(studentList.get(i).getName());
			row2.createCell(3);
			row2.createCell(4);
			row2.createCell(5);
			
			sheet.addMergedRegion(new CellRangeAddress(
		            3+11*i, //first row (0-based)
		            3+11*i, //last row  (0-based)
		            2, //first column (0-based)
		            5  //last column  (0-based)
		    ));
			row2.getCell(2).setCellStyle(style1);
			row2.getCell(3).setCellStyle(style3);
			row2.getCell(4).setCellStyle(style3);
			row2.getCell(5).setCellStyle(style3);
			//身份证号
			Row row3=sheet.createRow((short)(4+11*i));
			row3.createCell(1).setCellValue("身份证号");
			row3.getCell(1).setCellStyle(style2);
			row3.createCell(2).setCellValue(studentList.get(i).getIdentityCardNo());
			row3.createCell(3);
			row3.createCell(4);
			row3.createCell(5);
			
			sheet.addMergedRegion(new CellRangeAddress(
		            4+11*i, //first row (0-based)
		            4+11*i, //last row  (0-based)
		            2, //first column (0-based)
		            5  //last column  (0-based)
		    ));
			row3.getCell(2).setCellStyle(style1);
			row3.getCell(3).setCellStyle(style3);
			row3.getCell(4).setCellStyle(style3);
			row3.getCell(5).setCellStyle(style3);
			//工作单位
			Row row4=sheet.createRow((short)(5+11*i));
			row4.createCell(1).setCellValue("工作单位");
			row4.getCell(1).setCellStyle(style2);
			row4.createCell(2).setCellValue(studentList.get(i).getWorkUnit());
			row4.createCell(3);
			row4.createCell(4);
			row4.createCell(5);
			
			sheet.addMergedRegion(new CellRangeAddress(
		            5+11*i, //first row (0-based)
		            5+11*i, //last row  (0-based)
		            2, //first column (0-based)
		            5  //last column  (0-based)
		    ));
			row4.getCell(2).setCellStyle(style1);
			row4.getCell(3).setCellStyle(style3);
			row4.getCell(4).setCellStyle(style3);
			row4.getCell(5).setCellStyle(style3);
			//考场号  座位号
			Row row5=sheet.createRow((short)(6+11*i));
			row5.createCell(1).setCellValue("考场号");
			row5.getCell(1).setCellStyle(style2);
			row5.createCell(2).setCellValue(studentList.get(i).getKaochangHao());
			row5.getCell(2).setCellStyle(style1);
			row5.createCell(4).setCellValue("座位号");
			row5.getCell(4).setCellStyle(style2);
			row5.createCell(5).setCellValue(studentList.get(i).getSeatNo());
			row5.getCell(5).setCellStyle(style1);
			//报名编号
			Row row6=sheet.createRow((short)(8+11*i));
			row6.createCell(1).setCellValue("报名编号");
			row6.createCell(2).setCellValue(studentList.get(i).getBaomingNo());
			row6.createCell(3);
			row6.createCell(4);
			row6.createCell(5);
			
			sheet.addMergedRegion(new CellRangeAddress(
		            8+11*i, //first row (0-based)
		            8+11*i, //last row  (0-based)
		            2, //first column (0-based)
		            5  //last column  (0-based)
		    ));
			row6.getCell(2).setCellStyle(style1);
			row6.getCell(3).setCellStyle(style3);
			row6.getCell(4).setCellStyle(style3);
			row6.getCell(5).setCellStyle(style3);
			
			//考生图像
			//add picture data to this workbook.
		    InputStream is = new FileInputStream(studentList.get(i).getImagePath());
		    byte[] bytes = IOUtils.toByteArray(is);
		    int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
		    is.close();
		 CreationHelper helper = wb.getCreationHelper();
		// Create the drawing patriarch.  This is the top level container for all shapes. 
		Drawing drawing = sheet.createDrawingPatriarch();
		//add a picture shape
	    ClientAnchor anchor = helper.createClientAnchor();
	    //set top-left corner of the picture,
	    //subsequent call of Picture#resize() will operate relative to it
	    anchor.setRow1(1+11*i);
	    anchor.setRow2(9+11*i);
	    anchor.setCol1(6);
	    anchor.setCol2(7);
	    
	    Picture pict = drawing.createPicture(anchor, pictureIdx);    
	    //auto-size picture relative to its top-left corner
	    pict.resize();
	    
	    	//虚线分割线
	    CellStyle style4 = wb.createCellStyle();
	    style4.setBorderBottom(CellStyle.BORDER_DOTTED); 
	    Row row7=sheet.createRow(10+11*i);
	    row7.createCell(0).setCellStyle(style4);
	    row7.createCell(1).setCellStyle(style4);
	    row7.createCell(2).setCellStyle(style4);
	    row7.createCell(3).setCellStyle(style4);
	    row7.createCell(4).setCellStyle(style4);
	    row7.createCell(5).setCellStyle(style4);
	    row7.createCell(6).setCellStyle(style4);
	    row7.createCell(7).setCellStyle(style4);
		}
	}
	public static void main(String[] args) throws InvalidFormatException, IOException {
		SeatLabelGenerator seatLabelGenerator=new SeatLabelGenerator();
		List<SeatLabelInfo> studentList=new ArrayList<SeatLabelInfo>();
		//SeatLabelInfo student1 =new SeatLabelInfo("1333431Z600111","曾俊","420902198804221100","IBM","002",1,"3123123123123","D:/workspace/ZJUSCIX/WebContent/userfiles/image/1.jpg");
		//studentList.add(student1);
		//SeatLabelInfo student2 =new SeatLabelInfo("1333431Z600112","曾俊3","420902198804221100","IBM","002",6,"3123123123123","D:/workspace/ZJUSCIX/WebContent/userfiles/image/1.jpg");
		//studentList.add(student2);
		//SeatLabelInfo student3 =new SeatLabelInfo("1333431Z600113","曾俊4","420902198804221100","IBM","002",5,"3123123123123","D:/workspace/ZJUSCIX/WebContent/userfiles/image/1.jpg");
		//studentList.add(student3);
		//System.out.println("----------------------开始座位标签");
		//seatLabelGenerator.modify("./WebContent/seatlabel.xls", "./WebContent/seatlabelOK.xls", studentList);
		//System.out.println("----------------------座位标签生成完毕");
	}
}
